package com.example.playbox.utils

import android.annotation.SuppressLint
import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import com.example.playbox.bean.Student

class StudentDbHelper(context: Context, name: String, factory: SQLiteDatabase.CursorFactory?, version: Int) :
    SQLiteOpenHelper(context, name, factory, version) {

    companion object {
        const val DB_NAME = "student.db"
        const val TABLE_NAME = "students"
    }

    override fun onCreate(db: SQLiteDatabase) {
        val createTable = "CREATE TABLE $TABLE_NAME (" +
                "stuNumber TEXT PRIMARY KEY," +
                "stuName TEXT," +
                "stuMajor TEXT," +
                "stuPhone TEXT," +
                "stuQq TEXT," +
                "stuAddress TEXT)"
        db.execSQL(createTable)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS $TABLE_NAME")
        onCreate(db)
    }

    @SuppressLint("Range")
    fun readStudents(stuNumber: String): List<Student>? {
        val db = this.readableDatabase
        val cursor = db.query(TABLE_NAME, null, "stuNumber=?", arrayOf(stuNumber), null, null, null)
        val students = mutableListOf<Student>()
        if (cursor.moveToFirst()) {
            do {
                val student = Student(
                    cursor.getString(cursor.getColumnIndex("stuNumber")),
                    cursor.getString(cursor.getColumnIndex("stuName")),
                    cursor.getString(cursor.getColumnIndex("stuMajor")),
                    cursor.getString(cursor.getColumnIndex("stuPhone")),
                    cursor.getString(cursor.getColumnIndex("stuQq")),
                    cursor.getString(cursor.getColumnIndex("stuAddress"))
                )
                students.add(student)
            } while (cursor.moveToNext())
        }
        cursor.close()
        return if (students.isNotEmpty()) students else null
    }

    fun saveStudent(student: Student) {
        val db = this.writableDatabase
        val values = ContentValues().apply {
            put("stuNumber", student.stuNumber)
            put("stuName", student.stuName)
            put("stuMajor", student.stuMajor)
            put("stuPhone", student.stuPhone)
            put("stuQq", student.stuQq)
            put("stuAddress", student.stuAddress)
        }
        db.insertWithOnConflict(TABLE_NAME, null, values, SQLiteDatabase.CONFLICT_REPLACE)
    }
}